Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶

Angaben Studierende(r) (fehlende Angaben ergänzen)¶

Vorname: Silas
Nachname: Häffner
Immatrikulationsnummer: 24672354
Modul: Data Science
Prüfungsdatum / Raum / Zeit: 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45
Erlaubte Hilfsmittel: w.MA.XX.DS.24HS (Data Science)
Open Book, Eigener Computer, Internet-Zugang
Nicht erlaubt: Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT)
sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen.

Bewertungskriterien¶

(max. erreichbare Punkte: 48)¶

Kategorie Beschreibung Punkteverteilung
Code nicht lauffähig oder Ergebnisse nicht sinnvoll Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. 0 Punkte
Code lauffähig, aber mit gravierenden Mängeln Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. 25% der max. erreichbaren Punkte
Code lauffähig, aber mit mittleren Mängeln Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. 50% der max. erreichbaren Punkte
Code lauffähig, aber mit minimalen Mängeln Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. 75% der max. erreichbaren Punkte
Code lauffähig und korrekt Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. 100% der max. erreichbaren Punkte

Python Libraries und Settings¶

In [39]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text

# Ignore warnings
import warnings
warnings.filterwarnings("ignore")

print(os.getcwd())
/workspaces/python_postgresql_postgis

Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶

1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶

GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶

Hinweis:

  • Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
  • Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.

2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶

In [40]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"

# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
                    "@" + host + ":" + port + "/" + database

# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)

# Test der Connection
with engine.connect() as connection:
    result = connection.execute(text('SELECT current_database()'))
    print(result.fetchone())

# Verbindung trennen
engine.dispose()
('osm_switzerland',)

Aufgaben (Dieser Teil wird bewertet!)¶

Hinweise zu den folgenden Aufgabenstellungen:

  • In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
    1. Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
    2. Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
  • In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
  • In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Beachten Sie, dass für die Punktevergabe auch die weiteren Anforderungen zu den Fragen unter 'Details zur Aufgabenstellung' herangezogen werden.

Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
  • Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
  • Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [41]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            h.name,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.shop = 'car_repair'
      ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[41]:
osm_id shop name geom
0 1811755810 car_repair Grenzgarage POINT (9.62898 47.45412)
1 9408250312 car_repair Gebr. Wirth AG POINT (9.63098 47.45327)
2 5254765356 car_repair Richner AG POINT (9.43594 47.50471)
3 9408188280 car_repair Zehender Garage AG POINT (9.46826 47.49408)
4 3346119599 car_repair Garage Martino GmbH POINT (9.49096 47.47737)
... ... ... ... ...
1458 7524279282 car_repair None POINT (9.15641 47.54915)
1459 7523837743 car_repair None POINT (9.18904 47.55991)
1460 4366881989 car_repair Autoviva VW und Seat POINT (9.27967 47.54715)
1461 1493634725 car_repair Morandi Group AG POINT (9.19848 47.63967)
1462 1485836431 car_repair Toyota Garage Schlauri AG POINT (9.16553 47.64061)

1463 rows × 4 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [42]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[42]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [43]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.amenity,
            h.shop,
            h.name,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.amenity = 'biergarten'
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[43]:
osm_id amenity shop name geom
0 704467869 biergarten None Bierhalle POINT (9.6068 47.40694)
1 370319479 biergarten None Center da Surf POINT (9.79135 46.45518)
2 2467575051 biergarten None Le Bioley POINT (7.12275 46.09499)
3 746772927 biergarten None Buvette de l'alpage du col du Lein POINT (7.15997 46.11045)
4 370416485 biergarten None Bar des Etablons POINT (7.23621 46.14038)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [44]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[44]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
  • Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
  • Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
  • Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
  • Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.

(max. erreichbare Punkte: 6)

In [45]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
                p.osm_id,
                p."addr:street",
                p."addr:housenumber",
                p."addr:city",
                p."addr:postcode",
                p.building,
                st_transform(p.way, 4326) AS geom
        FROM
                public.planet_osm_polygon AS p
        WHERE
                p."addr:street" IS NOT NULL
                AND p."addr:housenumber" IS NOT NULL
                AND p."addr:city" IS NOT NULL
                AND p."addr:street" IN ('Stadthausstrasse')
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[45]:
osm_id addr:street addr:housenumber addr:city addr:postcode building geom
0 9264543 Stadthausstrasse 4a Winterthur 8400 government POLYGON ((8.73111 47.50115, 8.73112 47.50085, ...
1 22301937 Stadthausstrasse 4 Winterthur 8400 apartments POLYGON ((8.73232 47.50109, 8.73233 47.50107, ...
2 75027485 Stadthausstrasse 31 Winterthur 8400 office POLYGON ((8.73162 47.50041, 8.73165 47.50029, ...
3 75027472 Stadthausstrasse 35 Winterthur 8400 apartments POLYGON ((8.73153 47.5004, 8.73157 47.50026, 8...
4 75027503 Stadthausstrasse 37 Winterthur 8400 apartments POLYGON ((8.73146 47.5004, 8.73149 47.50028, 8...
... ... ... ... ... ... ... ...
71 86335426 Stadthausstrasse 4 Luzern 6003 apartments POLYGON ((8.30523 47.04936, 8.30531 47.04926, ...
72 86335481 Stadthausstrasse 2 Luzern 6003 apartments POLYGON ((8.30538 47.0494, 8.30545 47.04932, 8...
73 86335380 Stadthausstrasse 7 Luzern 6003 apartments POLYGON ((8.30491 47.04927, 8.30502 47.04915, ...
74 86335371 Stadthausstrasse 6 Luzern 6003 apartments POLYGON ((8.30509 47.04931, 8.3051 47.04929, 8...
75 134980581 Stadthausstrasse 10b Winterthur 8400 yes POLYGON ((8.72652 47.50075, 8.72661 47.50063, ...

76 rows × 7 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [46]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=17, 
               tiles='EsriWorldImagery')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[46]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [47]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            r.osm_id,
            r.highway,
            ST_Transform(r.way, 4326) AS geom
        FROM planet_osm_roads r
        WHERE r.highway = 'motorway';
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[47]:
osm_id highway geom
0 1236416666 motorway LINESTRING (9.64218 47.43433, 9.6422 47.43402,...
1 552469430 motorway LINESTRING (9.64232 47.43412, 9.6423 47.43469)
2 552469432 motorway LINESTRING (9.6423 47.43469, 9.64232 47.43504,...
3 552469428 motorway LINESTRING (9.64312 47.43793, 9.6429 47.43753,...
4 186132194 motorway LINESTRING (9.64235 47.43533, 9.6424 47.43568,...
... ... ... ...
8264 100501651 motorway LINESTRING (9.15834 47.65468, 9.15789 47.65424...
8265 14334868 motorway LINESTRING (9.15371 47.65075, 9.15343 47.6503,...
8266 20822469 motorway LINESTRING (9.14594 47.63688, 9.14599 47.63692...
8267 70908737 motorway LINESTRING (9.14357 47.63107, 9.14364 47.63151)
8268 1316916577 motorway LINESTRING (9.14365 47.63224, 9.14352 47.63152)

8269 rows × 3 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [48]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=9, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    line_weight=3,
    line_color='red'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[48]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features
  • Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.

(max. erreichbare Punkte: 8)

In [49]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            l.osm_id,
            l.waterway,
            ST_Transform(l.way, 4326) AS geom
        FROM planet_osm_line l
        WHERE l.waterway = 'river';
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[49]:
osm_id waterway geom
0 965608814 river LINESTRING (9.55554 47.58757, 9.55502 47.58628...
1 123157776 river LINESTRING (9.6523 47.45459, 9.64581 47.45564,...
2 197492692 river LINESTRING (9.59407 47.46441, 9.59392 47.46459...
3 947314919 river LINESTRING (9.56177 47.50449, 9.55057 47.53718...
4 1210578598 river LINESTRING (9.47697 47.48965, 9.47751 47.49004...
... ... ... ...
2553 151698226 river LINESTRING (8.87064 47.56425, 8.87076 47.56459)
2554 1070540790 river LINESTRING (8.87031 47.56279, 8.87036 47.56304)
2555 151698225 river LINESTRING (8.87036 47.56304, 8.87064 47.56425)
2556 145041304 river LINESTRING (9.12606 47.66731, 9.11666 47.6692,...
2557 514055688 river LINESTRING (9.64477 47.52537, 9.63639 47.52762...

2558 rows × 3 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [50]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[50]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
  • Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
  • Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [56]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            h.name,
            h.addr:city,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h."addr:city" = ('Winterthur', 'Zurich')
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
---------------------------------------------------------------------------
SyntaxError                               Traceback (most recent call last)
File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1967, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1966     if not evt_handled:
-> 1967         self.dialect.do_execute(
   1968             cursor, str_statement, effective_parameters, context
   1969         )
   1971 if self._has_events or self.engine._has_events:

File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/default.py:941, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    940 def do_execute(self, cursor, statement, parameters, context=None):
--> 941     cursor.execute(statement, parameters)

SyntaxError: syntax error at or near ":"
LINE 5:             h.addr:city,
                          ^


The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
Cell In[56], line 16
      5 sql = """SELECT
      6             h.osm_id,
      7             h.shop,
   (...)
     12         WHERE h."addr:city" = ('Winterthur')
     13      ;"""
     15 # Ergebnis in GeoDataFrame abspeichern
---> 16 gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
     18 # Datenbankverbindung trennen
     19 engine.dispose()

File ~/.local/lib/python3.12/site-packages/geopandas/geodataframe.py:817, in GeoDataFrame.from_postgis(cls, sql, con, geom_col, crs, index_col, coerce_float, parse_dates, params, chunksize)
    747 @classmethod
    748 def from_postgis(
    749     cls,
   (...)
    758     chunksize=None,
    759 ):
    760     """
    761     Alternate constructor to create a ``GeoDataFrame`` from a sql query
    762     containing a geometry column in WKB representation.
   (...)
    814     geopandas.read_postgis : read PostGIS database to GeoDataFrame
    815     """
--> 817     df = geopandas.io.sql._read_postgis(
    818         sql,
    819         con,
    820         geom_col=geom_col,
    821         crs=crs,
    822         index_col=index_col,
    823         coerce_float=coerce_float,
    824         parse_dates=parse_dates,
    825         params=params,
    826         chunksize=chunksize,
    827     )
    829     return df

File ~/.local/lib/python3.12/site-packages/geopandas/io/sql.py:185, in _read_postgis(sql, con, geom_col, crs, index_col, coerce_float, parse_dates, params, chunksize)
    135 """
    136 Returns a GeoDataFrame corresponding to the result of the query
    137 string, which must contain a geometry column in WKB representation.
   (...)
    180 >>> df = geopandas.read_postgis(sql, con)  # doctest: +SKIP
    181 """
    183 if chunksize is None:
    184     # read all in one chunk and return a single GeoDataFrame
--> 185     df = pd.read_sql(
    186         sql,
    187         con,
    188         index_col=index_col,
    189         coerce_float=coerce_float,
    190         parse_dates=parse_dates,
    191         params=params,
    192         chunksize=chunksize,
    193     )
    194     return _df_to_geodf(df, geom_col=geom_col, crs=crs, con=con)
    196 else:
    197     # read data in chunks and return a generator

File ~/.local/lib/python3.12/site-packages/pandas/io/sql.py:734, in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize, dtype_backend, dtype)
    724     return pandas_sql.read_table(
    725         sql,
    726         index_col=index_col,
   (...)
    731         dtype_backend=dtype_backend,
    732     )
    733 else:
--> 734     return pandas_sql.read_query(
    735         sql,
    736         index_col=index_col,
    737         params=params,
    738         coerce_float=coerce_float,
    739         parse_dates=parse_dates,
    740         chunksize=chunksize,
    741         dtype_backend=dtype_backend,
    742         dtype=dtype,
    743     )

File ~/.local/lib/python3.12/site-packages/pandas/io/sql.py:1836, in SQLDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype, dtype_backend)
   1779 def read_query(
   1780     self,
   1781     sql: str,
   (...)
   1788     dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
   1789 ) -> DataFrame | Iterator[DataFrame]:
   1790     """
   1791     Read SQL query into a DataFrame.
   1792 
   (...)
   1834 
   1835     """
-> 1836     result = self.execute(sql, params)
   1837     columns = result.keys()
   1839     if chunksize is not None:

File ~/.local/lib/python3.12/site-packages/pandas/io/sql.py:1659, in SQLDatabase.execute(self, sql, params)
   1657 args = [] if params is None else [params]
   1658 if isinstance(sql, str):
-> 1659     return self.con.exec_driver_sql(sql, *args)
   1660 return self.con.execute(sql, *args)

File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1779, in Connection.exec_driver_sql(self, statement, parameters, execution_options)
   1774 execution_options = self._execution_options.merge_with(
   1775     execution_options
   1776 )
   1778 dialect = self.dialect
-> 1779 ret = self._execute_context(
   1780     dialect,
   1781     dialect.execution_ctx_cls._init_statement,
   1782     statement,
   1783     None,
   1784     execution_options,
   1785     statement,
   1786     distilled_parameters,
   1787 )
   1789 return ret

File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1846, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1844     return self._exec_insertmany_context(dialect, context)
   1845 else:
-> 1846     return self._exec_single_context(
   1847         dialect, context, statement, parameters
   1848     )

File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1986, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1983     result = context._setup_result_proxy()
   1985 except BaseException as e:
-> 1986     self._handle_dbapi_exception(
   1987         e, str_statement, effective_parameters, cursor, context
   1988     )
   1990 return result

File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:2355, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context, is_sub_exec)
   2353 elif should_wrap:
   2354     assert sqlalchemy_exception is not None
-> 2355     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
   2356 else:
   2357     assert exc_info[1] is not None

File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py:1967, in Connection._exec_single_context(self, dialect, context, statement, parameters)
   1965                 break
   1966     if not evt_handled:
-> 1967         self.dialect.do_execute(
   1968             cursor, str_statement, effective_parameters, context
   1969         )
   1971 if self._has_events or self.engine._has_events:
   1972     self.dispatch.after_cursor_execute(
   1973         self,
   1974         cursor,
   (...)
   1978         context.executemany,
   1979     )

File ~/.local/lib/python3.12/site-packages/sqlalchemy/engine/default.py:941, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    940 def do_execute(self, cursor, statement, parameters, context=None):
--> 941     cursor.execute(statement, parameters)

ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near ":"
LINE 5:             h.addr:city,
                          ^

[SQL: SELECT
            h.osm_id,
            h.shop,
            h.name,
            h.addr:city,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h."addr:city" = ('Winterthur')
     ;]
(Background on this error at: https://sqlalche.me/e/20/f405)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [52]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=12, 
               tiles='ESRIWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[52]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶

Details zur Aufgabenstellung:

  • Sie finden die Daten in der Tabelle 'planet_osm_point'.
  • Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
  • Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [53]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            p.osm_id,
            p.shop,
            p.name,
            
            ST_Distance(
                ST_Transform(p.way, 4326)::geography,
                -- Central station coordinates
                ST_SetSRID(ST_MakePoint(8.72397, 47.50031), 4326)::geography
            ) AS distance_meters,
            ST_TRANSFORM(p.way, 4326) AS geom
        FROM
            planet_osm_point AS p
        WHERE
            p.shop = 'hairdresser'
            AND ST_DWithin(
                ST_Transform(p.way, 4326)::geography,
                -- Central station coordinates
                ST_SetSRID(ST_MakePoint(8.72397, 47.50031), 4326)::geography,
                500
            )
        ORDER BY distance_meters;
     ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[53]:
osm_id shop name distance_meters geom
0 3884033858 hairdresser TopHair/BRAUN Hair 104.579231 POINT (8.72315 47.50107)
1 599561752 hairdresser Orinad 162.173997 POINT (8.72525 47.49914)
2 771204398 hairdresser Gidor 175.756061 POINT (8.72552 47.50149)
3 4125136752 hairdresser Coiffeur Fernando 212.213655 POINT (8.72138 47.50106)
4 703431929 hairdresser Haarpalast Sagarra 217.380437 POINT (8.72683 47.50007)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [54]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=16, 
               tiles='ESRIWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[54]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶

In [55]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime

print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')
-----------------------------------
POSIX
Linux | 6.5.0-1025-azure
Datetime: 2024-10-07 09:36:47
Python Version: 3.12.1
IP Address: 127.0.0.1
-----------------------------------